"""
作者：Leagolas
日期：2023年06月04日
"""

import pymysql
from datetime import datetime
from matplotlib import pyplot as plt
import numpy as np

#顺利完成后的返回界面
def back1():
    print("--- 请选择下一步 ---")
    print("    0：返回菜单    ")
    print("    1：继续点单   ")
    print("--- 请选择下一步 ---")
    print("请选择:")
    inputt = input()
    if (inputt == '1'):
        function1()
    elif (inputt == '0'):
        pass
    else:
        print("输入错误")
        back1()

#功能一处理点单信息
def sql1(order):
    isRight = True
    records = order.split(';')

    connection = pymysql.connect(host='localhost', user='root', password=' ', database='milkteashop')
    cursor = connection.cursor()
    try:

        rawList = []
        rawNum = []
        for record in records:
            fields = record.split(',')
            if (len(fields) != 2):
                print("ERROR：点单信息输入错误!!!")
                print("\n" * 3)
                isRight = False

            if (fields[0][0] == 'S'):
                # 读取下单产品的配方
                cursor.execute("SELECT * FROM milkteashop.recipe where s_c_id = '%s'" % (fields[0]))
                recipe = cursor.fetchall()

                if (len(recipe) <= 0):
                    print("ERROR：商品id输入错误!!!")
                    print("\n" * 3)
                    isRight = False
                else:
                    # 对原料存货进行检验与扣除
                    for raw_material in recipe:
                        rawList.append(raw_material[1])
                        rawNum.append(raw_material[2])
            elif (fields[0][0] == 'R'):
                cursor.execute("SELECT * FROM milkteashop.raw_material where r_m_id = '%s'" % (fields[0]))
                recipe = cursor.fetchall()

                if (len(recipe) <= 0):
                    print("ERROR：配料id输入错误!!!")
                    print("\n" * 3)
                    isRight = False
                else:
                    if (fields[0] in rawList):
                        index = rawList.index(fields[0])
                        rawNum[index] = rawNum[index] + int(fields[1])
                    else:
                        rawList.append(fields[0])
                        rawNum.append(int(fields[1]))


        isSufficient = True
        lackingRawList = []
        rawInventory = []
        for i in range(len(rawList)):
            cursor.execute("SELECT r_m_name, inventory FROM milkteashop.raw_material where r_m_id = '%s'" % (rawList[i]))
            result = cursor.fetchall()[0]
            rawInventory.append(int(result[1]))
            if (int(result[1]) < rawNum[i]):
                isSufficient = False
                lackingRawList.append(result[0])
        if (isSufficient):
            #获取当天日期
            date = datetime.now().strftime('%Y-%m-%d')

            # 执行SQL语句
            cursor.execute(
                "SELECT serial_number FROM milkteashop.sale_records where record_date = '%s' and serial_number like " % (
                    date) + "'S%'")
            # 获取所有记录列表
            results = cursor.fetchall()
            if len(results) == 0:
                newSerialNum = "S00001"
            else:
                latestRecord = results[len(results) - 1]
                latestSerialNum = latestRecord[0]
                newSerialNum = 'S' + str(int(latestSerialNum[1:]) + 1).zfill(5)

            for record in records:
                fields = record.split(',')
                cursor.execute("INSERT INTO sale_records VALUES ('%s','%s','%s', %d)" % (
                    date, newSerialNum, fields[0], int(fields[1])));

            for i in range(len(rawList)):
                cursor.execute("UPDATE raw_material set inventory = %d where r_m_id = '%s';" % (
                    rawInventory[i] - rawNum[i], rawList[i]))
            print("已成功点单！！！")
        else:
            LackingRawStr = ''
            for raw in lackingRawList:
                LackingRawStr = LackingRawStr + raw + ' '
            print(LackingRawStr + "库存不足！！！")


    except Exception as e:
        isRight = False
        print("Warning:存在错误！！！")


    if (isRight):
        connection.commit()
        cursor.close()
        connection.close()
        back1()
    else:
        cursor.close()
        connection.close()
        function1()

#功能一接收点单信息
def function1():
    print("---现在开始点单---")
    print("请输入点单信息:>")
    order = input();
    if (order == '#'):
        pass
    else:
        sql1(order)

#顺利完成后的返回界面
def back2():
    print("--- 请选择下一步 ---")
    print("    0:返回菜单   ")
    print("    1:继续点单   ")
    print("--- 请选择下一步 ---")
    print("请选择:")
    inputt = input()
    if (inputt == '1'):
        function2()
    elif (inputt == '0'):
        pass
    else:
        print("输入错误")
        back2()

#功能二处理入库信息
def sql2(order):
    isRight = True
    date = datetime.now().strftime('%Y-%m-%d')
    records = order.split(';')

    connection = pymysql.connect(host='localhost', user='root', password=' ', database='milkteashop')
    cursor = connection.cursor()
    try:
        # 执行SQL语句
        cursor.execute("SELECT serial_number FROM milkteashop.purchase_records where record_date = '%s' and serial_number like " % (date) + "'P%'")
        # 获取所有记录列表
        results = cursor.fetchall()
        if len(results) == 0:
            newSerialNum = "P00001"
        else:
            latestRecord = results[len(results) - 1]
            latestSerialNum = latestRecord[0]
            newSerialNum = 'P' + str(int(latestSerialNum[1:]) + 1).zfill(5)


        for record in records:
            fields = record.split(',')
            cursor.execute("INSERT INTO purchase_records VALUES ('%s','%s','%s', %d)" % (date,newSerialNum,fields[0],int(fields[1])));
            connection.commit()

            #修改raw_material表中的inventory数值
            cursor.execute("SELECT inventory FROM milkteashop.raw_material where r_m_id = '%s'" % (fields[0]))
            old_inventory = cursor.fetchall()[0][0]
            cursor.execute("UPDATE raw_material set inventory = %d where r_m_id = '%s';" % (int(old_inventory) + int(fields[1]),fields[0]))
            connection.commit()

    except Exception as e:
        print("输入错误!!!")
        isRight = False


    if (isRight):
        connection.commit()
        cursor.close()
        connection.close()
        back2()
    else:
        cursor.close()
        connection.close()
        function2()

#功能二接收入库信息
def function2():
    print("---现在开始入库---")
    print("请输入入库信息:>")

    order = input()
    if (order == '#'):
        pass
    else:
        sql2(order)


#顺利完成后的返回界面
def back3_1_1():
    print("添加成功！！！")
    print("--- 请选择下一步 ---")
    print("    0:返回菜单   ")
    print("    1:继续添加   ")
    print("--- 请选择下一步 ---")
    print("请选择:")
    inputt = input()
    if (inputt == '1'):
        function3_1_1()
    elif (inputt == '0' or inputt == '#'):
        function3_1()
    else:
        print("输入错误")
        back3_1_1()

#商品清单增添
def function3_1_1():
    isRight = True
    connection = pymysql.connect(host='localhost', user='root', password=' ', database='milkteashop')
    cursor = connection.cursor()
    print("请输入要增加的商品[商品id,名字,种类,价格]:>")
    inf = input()
    if (inf == '#'):
        function3_1()
    print("请输入要增加的商品的配方[商品id,原料一id,原料一数量; 商品id,原料二id,...]:>")
    rec = input()
    if (inf == '#' or rec == '#'):
        function3_1()
    else:
        inf = inf.split(',')
        rec = rec.split(';')
        try:
            cursor.execute("INSERT INTO sale_commodity VALUES ('%s', '%s' ,'%s', %d);" % (inf[0], inf[1], inf[2], int(inf[3])))
            connection.commit()
            for recc in rec:
                recc = recc.split(',')
                cursor.execute("INSERT INTO recipe VALUES ('%s', '%s', %d);" % (recc[0], recc[1], int(recc[2])))
            connection.commit()

        except Exception as e:
            print("输入格式错误或商品已存在！！！")
            cursor.execute("delete from sale_commodity where s_c_id = '%s';" % (inf[0]))
            cursor.execute("delete from recipe where s_c_id = '%s';" % (inf[0]))
            connection.commit()
            isRight = False

        if (isRight):
            connection.commit()
            cursor.close()
            connection.close()
            back3_1_1()
        else:
            cursor.close()
            connection.close()
            function3_1_1()

#顺利完成后的返回界面
def back3_1_2():
    print("删除成功！！！")
    print("--- 请选择下一步 ---")
    print("    0:返回菜单   ")
    print("    1:继续删除   ")
    print("--- 请选择下一步 ---")
    print("请选择:")
    inputt = input()
    if (inputt == '1'):
        function3_1_2()
    elif (inputt == '0' or inputt == '#'):
        function3_1()
    else:
        print("输入错误")
        back3_1_2()

#商品清单删除
def function3_1_2():
    isRight = True
    connection = pymysql.connect(host='localhost', user='root', password=' ', database='milkteashop')
    cursor = connection.cursor()
    print("请输入要删去的商品[商品id 或 商品名字]:>")
    inf = input()
    if (inf == '#'):
        function3_1()
    else:
        try:
            print("select * from sale_commodity where s_c_id = '%s' or s_c_name = '%s';" % (inf, inf))
            cursor.execute(
                "select * from sale_commodity where s_c_id = '%s' or s_c_name = '%s';" % (inf, inf))
            results = cursor.fetchall()
            if (len(results) <= 0):
                print("商品不存在")
                isRight = False
            else:
                cursor.execute(
                    "delete from sale_commodity where s_c_id = '%s' or s_c_name = '%s';" % (inf, inf))
                warning = input("输入Y以确认删除:>")
                if (warning == 'Y'):
                    connection.commit()
        except Exception as e:
            print("输入错误！！！")
            isRight = False

        if (isRight):
            connection.commit()
            cursor.close()
            connection.close()
            back3_1_2()
        else:
            cursor.close()
            connection.close()
            function3_1_2()

#第二级功能选择界面
def function3_1():
    print("---请选择你的需求---")
    print("    1：增")
    print("    2：删")
    print("---请选择你的需求---")
    print("请选择需求:>")
    order = input()
    if (order == '#'):
        function3()
    elif (order == '1'):
        function3_1_1()
    elif (order == '2'):
        function3_1_2()
    else:
        function3_1()

#顺利完成后的返回界面
def back3_2_1():
    print("添加成功！！！")
    print("--- 请选择下一步 ---")
    print("    0:返回菜单   ")
    print("    1:继续添加   ")
    print("--- 请选择下一步 ---")
    print("请选择:")
    inputt = input()
    if (inputt == '1'):
        function3_2_1()
    elif (inputt == '0'or inputt == '#'):
        function3_2()
    else:
        print("输入错误")
        back3_2_1()

#原料清单增添
def function3_2_1():
    isRight = True
    connection = pymysql.connect(host='localhost', user='root', password=' ', database='milkteashop')
    cursor = connection.cursor()
    print("请输入要增加的库存[库存id,名字,种类,价格]:>")
    inf = input()
    if (inf == '#'):
        function3_2()
    else:
        inf = inf.split(',')
        try:
            cursor.execute("INSERT INTO raw_material VALUES ('%s', '%s' ,'%s', 0 ,%d);" % (inf[0], inf[1], inf[2], int(inf[3])))
            connection.commit()

        except Exception as e:
            print("输入格式错误或库存已存在！！！")
            isRight = False

        if (isRight):
            connection.commit()
            cursor.close()
            connection.close()
            back3_1_1()
        else:
            cursor.close()
            connection.close()
            function3_1_1()

#顺利完成后的返回界面
def back3_2_2():
    print("删除成功！！！")
    print("--- 请选择下一步 ---")
    print("    0:返回菜单   ")
    print("    1:继续删除   ")
    print("--- 请选择下一步 ---")
    print("请选择:")
    inputt = input()
    if (inputt == '1'):
        function3_2_2()
    elif (inputt == '0' or inputt == '#'):
        function3_2()
    else:
        print("输入错误")
        back3_2_2()

#原料清单删除
def function3_2_2():
    isRight = True
    connection = pymysql.connect(host='localhost', user='root', password=' ', database='milkteashop')
    cursor = connection.cursor()
    print("请输入要删去的库存[库存id 或 库存名字]:>")
    inf = input()
    if (inf == '#'):
        function3_1()
    else:
        try:
            cursor.execute(
                "select * from raw_material where r_m_id = '%s' or r_m_name = '%s';" % (inf, inf))
            results = cursor.fetchall()
            if (len(results) <= 0):
                print("库存不存在")
                isRight = False
            else:
                cursor.execute(
                    "delete from raw_material where r_m_id = '%s' or r_m_name = '%s';" % (inf, inf))
                warning = input("输入Y以确认删除:>")
                if (warning == 'Y'):
                    connection.commit()
        except Exception as e:
            print("输入错误！！！")
            isRight = False

        if (isRight):
            connection.commit()
            cursor.close()
            connection.close()
            back3_1_2()
        else:
            cursor.close()
            connection.close()
            function3_1_2()

#第二级功能选择界面
def function3_2():
    print("---请选择你的需求---")
    print("    1：增")
    print("    2：删")
    print("---请选择你的需求---")
    print("请选择需求:>")
    order = input()
    if (order == '#'):
        function3()
    elif (order == '1'):
        function3_2_1()
    elif (order == '2'):
        function3_2_2()

#第一级功能选择界面
def function3():
    print("---请选择你要管理的清单---")
    print("      1：商品清单")
    print("      2：库存清单")
    print("---请选择你要管理的清单---")
    print("请选择需求:>")
    order = input()
    if (order == '#'):
        pass
    elif (order == '1'):
        function3_1()
    elif (order == '2'):
        function3_2()


#顺利完成后的返回界面
def back4_1():
    print("数据展示完毕！！！")
    print("--- 请选择下一步 ---")
    print("    0:返回菜单   ")
    print("    1:继续查看饮品总销量   ")
    print("--- 请选择下一步 ---")
    print("请选择:")
    inputt = input()
    if (inputt == '1'):
        function4_1()
    elif (inputt == '0' or inputt == '#'):
        function4()
    else:
        print("输入错误")
        back4_1()

#总销量数据展示
def function4_1():
    isRight = True
    print("请输入时间范围:>")
    datespan = input()
    #根据时间范围进行数据提取
    if (datespan == '#'):
        function4_1()
    else:
        try:
            connection = pymysql.connect(host='localhost', user='root', password=' ', database='milkteashop')
            cursor = connection.cursor()
            if (datespan == '*'):
                cursor.execute(
                    "SELECT sc.s_c_name, case when sr.total_sale is null then 0 else sr.total_sale end as total_sale FROM sale_commodity AS sc "
                    "LEFT JOIN (SELECT id AS s_c_id, cast(SUM(num) as signed)AS total_sale FROM sale_records "
                    "WHERE id LIKE 'SC%'GROUP BY id) AS sr ON sc.s_c_id = sr.s_c_id ORDER BY total_sale DESC;")

            else:
                datespan = datespan.split(':')



                cursor.execute(
                    "SELECT sc.s_c_name, case when sr.total_sale is null then 0 else sr.total_sale end as total_sale FROM sale_commodity AS sc "
                    "LEFT JOIN (SELECT id AS s_c_id, cast(SUM(num) as signed)AS total_sale FROM sale_records "
                    "WHERE id LIKE 'SC%%'and record_date >= '%s' and record_date <= '%s' GROUP BY id ) AS sr ON sc.s_c_id = sr.s_c_id ORDER BY total_sale DESC;"
                    % (datespan[0], datespan[1]))
                # results = cursor.fetchall()

            results = cursor.fetchall()
            x_labels = [];
            y_values = [];

            for i in range(len(results)):
                x_labels.append(results[i][0])
                y_values.append(results[i][1])

            x_labels.reverse()
            y_values.reverse()

            #绘图
            plt.figure(figsize=(10, 7), tight_layout=True)
            plt.barh(x_labels, y_values)
            plt.xlabel("总销量", fontsize=16)
            plt.ylabel("商品名称", fontsize=16)
            if (datespan == '*'):
                plt.title("记录以来商品总销量直方图", fontsize=20)
            else:
                plt.title("%s至%s商品总销量直方图" % (datespan[0], datespan[1]), fontsize=20)
            plt.xticks(fontsize=13)
            plt.yticks(fontsize=13)
            for i in range(len(results)):
                plt.text(y_values[i] + y_values[len(y_values) - 1] / 50, i, '%.0f' % y_values[i], ha='center',
                         va='center', fontsize=16)

            # 汉字字体，优先使用楷体，找不到则使用黑体
            plt.rcParams['font.sans-serif'] = ['Kaitt', 'SimHei']
            # 正常显示负号
            plt.rcParams['axes.unicode_minus'] = False

            plt.tight_layout()
            plt.show()
        except Exception as e:
            isRight = False
            print("ERROR!!!请重试!!!")

        if (isRight):
            cursor.close()
            connection.close()
            back4_1()
        else:
            cursor.close()
            connection.close()
            function4_1()

#顺利完成后的返回界面
def back4_2():
    print("数据展示完毕！！！")
    print("--- 请选择下一步 ---")
    print("    0:返回菜单   ")
    print("    1:继续查看饮品总利润   ")
    print("--- 请选择下一步 ---")
    print("请选择:")
    inputt = input()
    if (inputt == '1'):
        function4_2()
    elif (inputt == '0' or inputt == '#'):
        function4()
    else:
        print("输入错误")
        back4_2()

#总利润数据展示
def function4_2():
    isRight = True
    print("请输入时间范围:>")
    datespan = input()
    #根据时间范围进行数据提取
    if (datespan == '#'):
        function4_1()
    else:
        try:
            connection = pymysql.connect(host='localhost', user='root', password=' ', database='milkteashop')
            cursor = connection.cursor()
            if (datespan == '*'):
                cursor.execute(
                    "SELECT scp.s_c_name, (profit * total_sale) AS total_income FROM (SELECT com.s_c_id, s_c_name,  (sale_price - cost) AS profit FROM sale_commodity AS com "
                    "LEFT JOIN (SELECT rec.s_c_id, SUM(r_m_num * purchase_price) AS cost FROM recipe AS rec LEFT JOIN raw_material AS raw ON raw.r_m_id = rec.r_m_id "
                    "GROUP BY s_c_id) AS t ON com.s_c_id = t.s_c_id order by profit desc) AS scp "
                    "LEFT JOIN (SELECT sc.s_c_id, sc.s_c_name,  case when sr.total_sale is null then 0 else sr.total_sale end as total_sale FROM sale_commodity AS sc "
                    "LEFT JOIN (SELECT id AS s_c_id, cast(SUM(num) as signed) AS total_sale FROM sale_records WHERE id LIKE 'SC%' GROUP BY id) AS sr ON sc.s_c_id = sr.s_c_id "
                    "ORDER BY total_sale DESC) AS scs ON scp.s_c_id = scs.s_c_id ORDER BY total_income DESC; ")

            else:
                datespan = datespan.split(':')
                cursor.execute(
                        "SELECT scp.s_c_name, (profit * total_sale) AS total_income FROM (SELECT com.s_c_id, s_c_name,  (sale_price - cost) AS profit FROM sale_commodity AS com "
                        "LEFT JOIN (SELECT rec.s_c_id, SUM(r_m_num * purchase_price) AS cost FROM recipe AS rec LEFT JOIN raw_material AS raw ON raw.r_m_id = rec.r_m_id "
                        "GROUP BY s_c_id) AS t ON com.s_c_id = t.s_c_id order by profit desc) AS scp "
                        "LEFT JOIN (SELECT sc.s_c_id, sc.s_c_name,  case when sr.total_sale is null then 0 else sr.total_sale end as total_sale FROM sale_commodity AS sc "
                        "LEFT JOIN (SELECT id AS s_c_id, cast(SUM(num) as signed) AS total_sale FROM sale_records WHERE id LIKE 'SC%%' and record_date >= '%s' and record_date <= '%s'   GROUP BY id) AS sr ON sc.s_c_id = sr.s_c_id "
                        "ORDER BY total_sale DESC) AS scs ON scp.s_c_id = scs.s_c_id ORDER BY total_income DESC; "  % (datespan[0], datespan[1]))

            results = cursor.fetchall()
            x_labels = [];
            y_values = [];

            for i in range(len(results)):
                x_labels.append(results[i][0])
                y_values.append(results[i][1])

            x_labels.reverse()
            y_values.reverse()

            #绘图
            plt.figure(figsize=(10, 7), tight_layout=True)

            plt.barh(x_labels, y_values)

            plt.xlabel("总利润", fontsize=16)
            plt.ylabel("商品名称", fontsize=16)
            if (datespan == '*'):
                plt.title("记录以来商品总利润直方图", fontsize=20)
            else:
                plt.title("%s至%s商品总利润直方图" % (datespan[0], datespan[1]), fontsize=20)
            plt.xticks(fontsize=13)
            plt.yticks(fontsize=13)
            for i in range(len(results)):
                plt.text(y_values[i] + y_values[len(y_values) - 1] / 40, i, '%.0f' % y_values[i], ha='center',
                         va='center', fontsize=16)

            # 汉字字体，优先使用楷体，找不到则使用黑体
            plt.rcParams['font.sans-serif'] = ['Kaitt', 'SimHei']
            # 正常显示负号
            plt.rcParams['axes.unicode_minus'] = False

            plt.tight_layout()
            plt.show()
        except Exception as e:
            isRight = False
            print("ERROR!!!请重试!!!")


        if (isRight):
            cursor.close()
            connection.close()
            back4_2()
        else:
            cursor.close()
            connection.close()
            function4_2()

#功能选择界面
def function4():
    print("---请选择你要查看的数据---")
    print("       1：商品总销量")
    print("       2：商品总利润")
    print("---请选择你要查看的数据---")
    print("请选择需求:>")
    order = input()
    if (order == '#'):
        pass
    elif (order == '1'):
        function4_1()
    elif (order == '2'):
        function4_2()
    else:
        function4()
